# server.py
from flask import Flask, request, jsonify, send_file
import os
import pyodbc
import base64
import requests
import logging
import chardet
from datetime import datetime
import re
from dateutil import parser
import unicodedata

app = Flask(__name__)
logging.basicConfig(level=logging.DEBUG)

# 数据库连接配置
server = '127.0.0.1'
database = '1'
username = 'sa'
password = 'cj12032043'
driver = '{ODBC Driver 17 for SQL Server}'


def create_connection():
    try:
        conn = pyodbc.connect(f'DRIVER={driver};SERVER={server};DATABASE={database};UID={username};PWD={password}')
        logging.info("数据库连接成功！")
        return conn
    except pyodbc.Error as e:
        logging.error(f"数据库连接失败: {e}")
        return None


# 百度OCR配置
API_KEY = 'W1zEJLLX5sJMqGUmbRAZ0fWj'
SECRET_KEY = '2EzMj5aBzDLUeb5BMGWUFZGXAdZ5kCvG'
OCR_URL = 'https://aip.baidubce.com/rest/2.0/ocr/v1/vat_invoice'


def get_access_token():
    """
    获取百度OCR的访问令牌
    """
    url = f'https://aip.baidubce.com/oauth/2.0/token?grant_type=client_credentials&client_id={API_KEY}&client_secret={SECRET_KEY}'
    response = requests.get(url)
    if response.status_code == 200:
        return response.json().get('access_token')
    else:
        logging.error(f"获取访问令牌失败: {response.text}")
        return None


@app.route('/upload', methods=['POST'])
def upload_invoice():
    try:
        if 'file' not in request.files:
            return jsonify({'error': '未找到文件'}), 400
        file = request.files['file']
        if file.filename == '':
            return jsonify({'error': '未选择文件'}), 400
        if file:
            upload_folder = 'uploads'
            if not os.path.exists(upload_folder):
                os.makedirs(upload_folder)
            file_path = os.path.join(upload_folder, file.filename)
            file.save(file_path)
            abs_file_path = os.path.abspath(file_path)
            abs_file_path = abs_file_path.strip()
            if not abs_file_path:
                raise ValueError("文件路径无效")
            if not os.path.exists(abs_file_path):
                raise ValueError("文件路径不存在")
            access_token = get_access_token()
            if not access_token:
                return jsonify({'error': '获取访问令牌失败'}), 500
            with open(file_path, 'rb') as f:
                image_data = f.read()
                base64_data = base64.b64encode(image_data).decode('utf-8')
            url = f'{OCR_URL}?access_token={access_token}'
            headers = {'Content-Type': 'application/x-www-form-urlencoded'}
            data = {'image': base64_data}
            response = requests.post(url, headers=headers, data=data)
            if response.status_code == 200:
                try:
                    result = response.json()
                except ValueError:
                    logging.error(f"百度OCR接口返回非JSON格式数据: {response.text}")
                    return jsonify({'error': '百度OCR接口返回非JSON格式数据'}), 500
                if 'error_code' in result or 'error_msg' in result:
                    error_msg = result.get('error_msg', 'OCR接口返回错误')
                    logging.error(f"OCR识别错误: {error_msg}")
                    return jsonify({'error': f'OCR识别失败: {error_msg}'}), 400
                logging.debug(f"OCR原始响应: {result}")
                date_fields = ['invoice_date', 'date', '开票日期', 'InvoiceDate']
                invoice_date_str = None
                if isinstance(result, dict) and 'words_result' in result:
                    words_result = result['words_result']
                    if isinstance(words_result, dict):
                        for field in date_fields:
                            if field in words_result and words_result[field]:
                                invoice_date_str = words_result[field]
                                break
                    elif isinstance(words_result, list):
                        for item in words_result:
                            if isinstance(item, dict):
                                for field in date_fields:
                                    if field in item and item[field]:
                                        invoice_date_str = item[field]
                                        break
                                if invoice_date_str:
                                    break
                if not invoice_date_str:
                    if isinstance(result, dict) and 'words_result' in result:
                        invoice_date_str = extract_date_from_words(result['words_result'])
                    if not invoice_date_str:
                        logging.error("OCR未识别到任何日期字段")
                        return jsonify({'error': '未识别到发票日期，请检查发票图片'}), 400
                if isinstance(result, dict) and 'words_result' in result:
                    words_result = result['words_result']
                    invoice_code = words_result.get('InvoiceCode', '').strip() if isinstance(words_result, dict) else ''
                    invoice_number = words_result.get('InvoiceNum', '').strip() if isinstance(words_result, dict) else ''
                    commodity_name_list = words_result.get('CommodityName', [])
                    commodity_name = ''
                    if commodity_name_list:
                        if isinstance(commodity_name_list[0], str):
                            commodity_name = commodity_name_list[0].strip()
                        elif isinstance(commodity_name_list[0], dict):
                            commodity_name = commodity_name_list[0].get('word', '').strip() if 'word' in commodity_name_list[0] else '未知'
                    else:
                        commodity_name = '未知'
                    if not invoice_code:
                        invoice_code = '未知'
                    if not invoice_number:
                        invoice_number = '未知'
                else:
                    logging.error("OCR结果解析失败")
                    return jsonify({'error': 'OCR结果解析失败'}), 500
                invoice_date_str = unicodedata.normalize('NFKD', invoice_date_str).encode('ASCII', 'ignore').decode('utf-8')
                char_replacements = {'l': '1', 'I': '1', '０': '0', '１': '1', '２': '2', '３': '3', '４': '4', '５': '5',
                                     '６': '6', '７': '7', '８': '8', '９': '9'}
                for char, replacement in char_replacements.items():
                    invoice_date_str = invoice_date_str.replace(char, replacement)
                invoice_date_str = invoice_date_str.replace('年', '-').replace('月', '-').replace('日', '')
                invoice_date_str = re.sub(r'[^\d-]', '', invoice_date_str)
                if re.match(r'^\d{8}$', invoice_date_str):
                    invoice_date_str = f"{invoice_date_str[:4]}-{invoice_date_str[4:6]}-{invoice_date_str[6:]}"
                logging.debug(f"原始日期字符串: {invoice_date_str}")
                date_patterns = [
                    r'^\d{4}-\d{1,2}-\d{1,2}$',
                    r'^\d{4}[/-]\d{1,2}[/-]\d{1,2}$',
                    r'^[一二三四五六七八九十]{1,4}[年/-][一二三四五六七八九十]{1,2}[月/-][一二三四五六七八九十]{1,2}$',
                    r'^\d{4}-\d{2}-\d{2}T\d{2}:\d{2}:\d{2}([+-]\d{2}:\d{2}|Z)$'
                ]
                valid_date = None
                for pattern in date_patterns:
                    if re.match(pattern, invoice_date_str):
                        try:
                            valid_date = parser.parse(invoice_date_str, fuzzy=True).date()
                            break
                        except (parser.ParserError, ValueError):
                            continue
                if not valid_date:
                    logging.error(f"发票日期格式错误: {invoice_date_str}，格式不符合要求")
                    valid_date = datetime.strptime('1970-01-01', '%Y-%m-%d').date()
                if not (1800 <= valid_date.year <= 2200 and 1 <= valid_date.month <= 12 and 1 <= valid_date.day <= 31):
                    raise ValueError("日期超出合理范围")
                formatted_date = valid_date.strftime('%Y-%m-%d')
                conn = create_connection()
                if not conn:
                    return jsonify({'error': '数据库连接失败'}), 500
                cursor = conn.cursor()
                try:
                    # 修改此处，将Amount字段的值改为AmountInFiguers中的数值
                    cursor.execute(
                        "INSERT INTO dbo.Invoices (InvoiceCode, InvoiceNumber, Date, Amount, ImagePath, InvoiceType, CommodityName) VALUES (?,?,?,?,?,?,?)",
                        (
                            invoice_code,
                            invoice_number,
                            formatted_date,
                            result.get('words_result', {}).get('AmountInFiguers', ''),
                            abs_file_path,
                            result.get('words_result', {}).get('InvoiceType', ''),
                            commodity_name
                        )
                    )
                    conn.commit()
                except pyodbc.Error as e:
                    conn.rollback()
                    logging.error(f"数据库插入失败: {e}")
                    return jsonify({'error': '保存发票数据失败'}), 500
                finally:
                    cursor.close()
                    conn.close()
                return jsonify({
                   'success': True,
                    'invoice_code': invoice_code,
                    'invoice_number': invoice_number,
                    'invoice_date': formatted_date
                }), 200
    except Exception as e:
        logging.error(f"上传发票时发生错误: {str(e)}", exc_info=True)
        return jsonify({'error': f'上传发票时发生错误: {str(e)}'}), 500


@app.route('/query', methods=['GET'])
def query_invoices():
    try:
        start_date = request.args.get('start_date')
        end_date = request.args.get('end_date')
        keyword = request.args.get('keyword')
        query = "SELECT * FROM dbo.Invoices WHERE 1=1"
        params = []
        date_pattern = r'^\d{4}-\d{2}-\d{2}$'
        if start_date and not re.match(date_pattern, start_date):
            return jsonify({'error': '开始日期格式错误，应输入YYYY-MM-DD格式'}), 400
        if end_date and not re.match(date_pattern, end_date):
            return jsonify({'error': '结束日期格式错误，应输入YYYY-MM-DD格式'}), 400
        if start_date:
            query += " AND Date >=?"
            params.append(start_date)
        if end_date:
            query += " AND Date <=?"
            params.append(end_date)
        if keyword:
            keyword = re.sub(r'[^\w\u4e00-\u9fff]', '', keyword)
            keyword_pattern = f'%{keyword}%'
            query += " AND (UPPER(REPLACE(InvoiceCode, ' ', '')) LIKE UPPER(REPLACE(?, ' ', '')) OR UPPER(REPLACE(InvoiceNumber, ' ', '')) LIKE UPPER(REPLACE(?, ' ', '')) OR UPPER(REPLACE(CommodityName, ' ', '')) LIKE UPPER(REPLACE(?, ' ', '')))"
            params.extend([keyword_pattern, keyword_pattern, keyword_pattern])
        conn = create_connection()
        if not conn:
            return jsonify({'error': '数据库连接失败'}), 500
        cursor = conn.cursor()
        try:
            cursor.execute(query, params)
            results = []
            columns = [column[0] for column in cursor.description]
            for row in cursor.fetchall():
                results.append({
                    'id': row[0],
                    'invoice_code': row[1],
                    'invoice_number': row[2],
                    'invoice_date': row[3].strftime('%Y-%m-%d') if row[3] else '',
                    'amount': row[4],
                    'image_path': row[5],
                    'invoice_type': row[6] if len(row) > 6 else '',
                    'commodity_name': row[7] if len(row) > 7 else ''
                })
        except pyodbc.Error as e:
            logging.error(f"执行查询时出错: {e}")
            return jsonify({'error': f'执行查询时出错: {str(e)}'}), 500
        finally:
            cursor.close()
            conn.close()
        return jsonify(results), 200
    except Exception as e:
        logging.error(f"查询发票时出错: {str(e)}", exc_info=True)
        return jsonify({'error': f'查询发票时出错: {str(e)}'}), 500


@app.route('/statistics', methods=['GET'])
def statistics_invoices():
    try:
        start_date = request.args.get('start_date')
        end_date = request.args.get('end_date')
        keyword = request.args.get('keyword')
        date_pattern = r'^\d{4}-\d{2}-\d{2}$'
        if start_date and not re.match(date_pattern, start_date):
            return jsonify({'error': '开始日期格式错误，应输入YYYY-MM-DD格式'}), 400
        if end_date and not re.match(date_pattern, end_date):
            return jsonify({'error': '结束日期格式错误，应输入YYYY-MM-DD格式'}), 400
        query = "SELECT COUNT(*), SUM(CAST(Amount AS FLOAT)) FROM dbo.Invoices WHERE 1=1"
        params = []
        if start_date:
            query += " AND Date >=?"
            params.append(start_date)
        if end_date:
            query += " AND Date <=?"
            params.append(end_date)
        if keyword:
            keyword = re.sub(r'[^\w\u4e00-\u9fff]', '', keyword)
            keyword_pattern = f'%{keyword}%'
            query += " AND (UPPER(REPLACE(InvoiceCode, ' ', '')) LIKE UPPER(REPLACE(?, ' ', '')) OR UPPER(REPLACE(InvoiceNumber, ' ', '')) LIKE UPPER(REPLACE(?, ' ', '')) OR UPPER(REPLACE(CommodityName, ' ', '')) LIKE UPPER(REPLACE(?, ' ', '')))"
            params.extend([keyword_pattern, keyword_pattern, keyword_pattern])
        conn = create_connection()
        if not conn:
            return jsonify({'error': '数据库连接失败'}), 500
        cursor = conn.cursor()
        try:
            cursor.execute(query, params)
            result = cursor.fetchone()
            invoice_count = result[0]
            total_amount = result[1] if result[1] else 0
        except pyodbc.Error as e:
            logging.error(f"执行统计查询时出错: {e}")
            return jsonify({'error': f'执行统计查询时出错: {str(e)}'}), 500
        finally:
            cursor.close()
            conn.close()
        return jsonify({'invoice_count': invoice_count, 'total_amount': total_amount}), 200
    except Exception as e:
        logging.error(f"统计发票时出错: {str(e)}", exc_info=True)
        return jsonify({'error': f'统计发票时出错: {str(e)}'}), 500


def extract_date_from_words(words_result):
    """
    从OCR识别的文本中提取日期
    """
    date_pattern = r'\d{4}[年/-]\d{1,2}[月/-]\d{1,2}|[一二三四五六七八九十]{1,4}[年/-][一二三四五六七八九十]{1,2}[月/-][一二三四五六七八九十]{1,2}'
    if isinstance(words_result, dict):
        for key, value in words_result.items():
            if isinstance(value, str):
                match = re.search(date_pattern, value)
                if match:
                    return match.group()
    elif isinstance(words_result, list):
        for item in words_result:
            text = item.get('words', '')
            match = re.search(date_pattern, text)
            if match:
                return match.group()
    return None


if __name__ == '__main__':
    app.run(debug=True)